import sqlite3
import os

def initialize_database(db_name="aidemo.db", db_dir="data"):
    """
    Initializes an SQLite database with 'products', 'orders', and 'order_details' tables.

    Args:
        db_name (str): The name of the database file.
        db_dir (str): The directory where the database file will be stored.
    """

    # Create the data directory if it doesn't exist
    if not os.path.exists(db_dir):
        os.makedirs(db_dir)

    db_path = os.path.join(db_dir, db_name)
    conn = None  # Initialize conn to None
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # --- Create 'products' table ---
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS products (
                product_id TEXT PRIMARY KEY,
                product_name TEXT NOT NULL,
                product_type TEXT,
                specification TEXT,
                price REAL NOT NULL,
                stock_quantity INTEGER NOT NULL,
                description TEXT
            );
        """)

        # Insert sample data into 'products'
        cursor.execute("INSERT OR IGNORE INTO products VALUES (?, ?, ?, ?, ?, ?, ?)",
                       ('001', '五粮液', '白酒', '1箱*4瓶', 100.0, 800, '普五八代')) # Changed price to 100.0 and stock to 800
        cursor.execute("INSERT OR IGNORE INTO products VALUES (?, ?, ?, ?, ?, ?, ?)",
                       ('002', '茅台', '白酒', '1箱*6瓶', 200.0, 500, '飞天茅台'))
        cursor.execute("INSERT OR IGNORE INTO products VALUES (?, ?, ?, ?, ?, ?, ?)",
                       ('003', '剑南春', '白酒', '1箱*4瓶', 80.0, 1200, '东方红'))

        # --- Create 'orders' table ---
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS orders (
                order_id TEXT PRIMARY KEY,
                purchaser TEXT NOT NULL,
                total_amount REAL NOT NULL,
                order_time TEXT NOT NULL,
                delivery_time TEXT,
                is_settled INTEGER NOT NULL
            );
        """)

        # Insert sample data into 'orders'
        cursor.execute("INSERT OR IGNORE INTO orders VALUES (?, ?, ?, ?, ?, ?)",
                       ('20250605001', '上海白金酒店管理有限公司', 56400.0, '2025-06-05 10:00:00', '2025-06-05 18:00:00', 1))
        cursor.execute("INSERT OR IGNORE INTO orders VALUES (?, ?, ?, ?, ?, ?)",
                       ('20250605002', '北京四季酒店', 30000.0, '2025-06-05 11:30:00', None, 0))
        cursor.execute("INSERT OR IGNORE INTO orders VALUES (?, ?, ?, ?, ?, ?)",
                       ('20250605003', '广州喜来登酒店', 15000.0, '2025-06-05 12:00:00', None, 0))


        # --- Create 'order_details' table ---
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS order_details (
                detail_id TEXT PRIMARY KEY,
                order_id TEXT NOT NULL,
                product_id TEXT NOT NULL,
                quantity INTEGER NOT NULL,
                price REAL NOT NULL,
                amount REAL NOT NULL,
                FOREIGN KEY (order_id) REFERENCES orders(order_id),
                FOREIGN KEY (product_id) REFERENCES products(product_id)
            );
        """)

        # Insert sample data into 'order_details'
        cursor.execute("INSERT OR IGNORE INTO order_details VALUES (?, ?, ?, ?, ?, ?)",
                       ('D001', '20250605001', '001', 500, 100.0, 50000.0))
        cursor.execute("INSERT OR IGNORE INTO order_details VALUES (?, ?, ?, ?, ?, ?)",
                       ('D002', '20250605001', '002', 20, 200.0, 4000.0))
        cursor.execute("INSERT OR IGNORE INTO order_details VALUES (?, ?, ?, ?, ?, ?)",
                       ('D003', '20250605001', '003', 30, 80.0, 2400.0))
        cursor.execute("INSERT OR IGNORE INTO order_details VALUES (?, ?, ?, ?, ?, ?)",
                       ('D004', '20250605002', '001', 200, 100.0, 20000.0))
        cursor.execute("INSERT OR IGNORE INTO order_details VALUES (?, ?, ?, ?, ?, ?)",
                       ('D005', '20250605002', '002', 50, 200.0, 10000.0))
        cursor.execute("INSERT OR IGNORE INTO order_details VALUES (?, ?, ?, ?, ?, ?)",
                       ('D006', '20250605003', '003', 150, 80.0, 12000.0))
        cursor.execute("INSERT OR IGNORE INTO order_details VALUES (?, ?, ?, ?, ?, ?)",
                       ('D007', '20250605003', '001', 30, 100.0, 3000.0))


        conn.commit()
        print(f"Database '{db_name}' initialized successfully in the '{db_dir}' directory.")
        print("Tables 'products', 'orders', and 'order_details' created and populated with sample data.")

    except sqlite3.Error as e:
        print(f"An error occurred: {e}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    initialize_database()